<?php
declare(strict_types=1);
namespace Framework\Support;
use Swoole\Table;

class SqliteDb
{
    private string $name;
    private \PDO $link;  // 写连接

    public function __construct($name)
    {
        $this->name = $name;
        $this->link =  new \PDO('sqlite:'. $name);;
    }

    public function _T():Table
    {
        return $_ENV['Table']['SqlStat'];
    }

    public function sql_find_one(array $sql)
    {
        return $this->query($sql,1);
    }

    public function sql_find(array $sql, $key = NULL)
    {
        $arrlist = $this->query($sql,2);
        $key AND $arrlist = arrlist_change_key($arrlist, $key);
        return !empty($arrlist) ? $arrlist:[];
    }

    public function find(string $table,array $cond = [],array $orderby = [],int $page = 0,int $pagesize = 0,string $key = '',array $col = [],string $group = '')
    {
        $page = min(max(0, $page), 200000000);
        $pagesize = min(max(0, $pagesize), 1000000);
        $cond = db_cond_to_sqladd($cond);

        $orderby = db_orderby_to_sqladd($orderby);
        !empty($orderby['val']) && array_push($cond['val'],...$orderby['val']);
        $offset = ($page > 0 || $pagesize > 0) ? 'LIMIT ' . (($page - 1) * $pagesize) . ',' . $pagesize : '';
        if (!empty($col)) {
            $cols = is_array($col) ? implode(',', $col) : $col;
        } else {
            $cols = '*';
        }

        $data = [
            'sql'=>	'SELECT '.$cols.' FROM '.$table.' '.$cond['sql'].' '.$group.' '.$orderby['sql'].' '. $offset,
            'val'=>$cond['val']
        ];

        return $this->sql_find($data, $key);
    }

    public function find_one(string $table,array $cond = [],array $orderby = [],array $col = [])
    {
        $cond = db_cond_to_sqladd($cond);
        $orderby = db_orderby_to_sqladd($orderby);
        !empty($orderby['val']) && array_push($cond['val'],...$orderby['val']);
        if (!empty($col)) {
            $cols = is_array($col) ? implode(',', $col) : $col;
        } else {
            $cols = '*';
        }
        $data = [
            'sql'=>	'SELECT '.$cols.' FROM '.$table.' '.$cond['sql'].' '. $orderby['sql'].' LIMIT 1',
            'val'=>$cond['val']
        ];
        return $this->sql_find_one($data);
    }

    public function transaction(callable $closure):bool
    {
        $r=false;
        try {
            $this->link->beginTransaction();
            $closure($this->link);
            $this->link->commit();
            $r=true;
        }catch (\PDOException $err){
            $r=false;
            $this->link->rollBack();
            xn_log($err->getMessage(),'rollBack');
        }
        return $r;
    }
    public function query(array $sql,int $type):mixed
    {

        $data=[];
        try {
            $t1 = get_ut();
            $query = $this->link->prepare($sql['sql']);
            $query->execute($sql['val']);
            $data = $type==1 ? $query->fetch(\PDO::FETCH_ASSOC) : $query->fetchAll(\PDO::FETCH_ASSOC);
            $t2 = get_ut();
            $use_time = bcsub((string)$t2, (string)$t1, 5);
            $use_time > 1 && xn_log($use_time . ' ' . $sql['sql'], 'slow_query');
        } catch (\Throwable $e) {
            xn_log($e->getMessage() . "\tSQL:" . $sql['sql'].' '.xn_json_encode($sql['val']), 'query_error');
        }
        return $data;
    }

    public function exec(array $sql):mixed
    {

        $n = false;
        try {
            $t1 = get_ut();
            $query = $this->link->prepare($sql['sql']);
            $n = $query->execute($sql['val']);
            $pre = strtoupper(substr($sql['sql'], 0, 7));
            if ($pre == 'INSERT ' || $pre == 'REPLACE') {
                $n = $this->link->lastinsertid();
            }
            $t2 = get_ut();
            $use_time = bcsub((string)$t2, (string)$t1, 5);
            $use_time > 1 && xn_log($use_time . ' ' . $sql['sql'], 'slow_query');
        } catch (\Throwable $e) {
            xn_log($e->getMessage() . "\t" . $sql['sql'].' '.xn_json_encode($sql['val']), 'exec_error');
            return false;
        }
        return $n;
    }

    public function count(string $table,array $cond = [],string $x = '*'):int
    {
        $sqladd = db_cond_to_sqladd($cond);
        $sql = ['sql'=>'SELECT COUNT('.$x.') AS num FROM '.$table.' '.$sqladd['sql'],'val'=>$sqladd['val']];
        $arr = $this->sql_find_one($sql);
        return !empty($arr) ? intval($arr['num']) : 0;
    }

    public function Max(string $table,string $field,array $cond = []):int
    {
        $sqladd = db_cond_to_sqladd($cond);
        $sql = ['sql'=>'SELECT MAX('.$field.') AS maxid FROM '.$table .' '.$sqladd['sql'],'val'=>$sqladd['val']];
        $arr = $this->sql_find_one($sql);
        return !empty($arr) ? intval($arr['maxid']) : 0;
    }
    public function Min(string $table,string $field,array $cond = []):int
    {
        $sqladd = db_cond_to_sqladd($cond);
        $sql = ['sql'=>'SELECT MIN('.$field.') AS minid FROM '.$table .' '.$sqladd['sql'],'val'=>$sqladd['val']];
        $arr = $this->sql_find_one($sql);
        return !empty($arr) ? intval($arr['minid']) : 0;
    }

    public function truncate(string $table):bool
    {
        return $this->exec(['sql'=>"TRUNCATE $table",'val'=>[]]);
    }

    public function sum(string $table,string $sum, array $cond = []):int
    {
        $sqladd = db_cond_to_sqladd($cond);
        $sql = ['sql'=>'SELECT SUM('.$sum.') AS num FROM '.$table.' ' .$sqladd['sql'],'val'=>$sqladd['val']];
        $r = $this->sql_find_one($sql);
        return !empty($r['num']) ? $r['num'] : 0;
    }

    public function create(string $table,array $arr):bool
    {
        return $this->insert($table, $arr);
    }

    public function insert(string $table,array $arr):mixed
    {
        $sqladd = db_array_to_insert_sqladd($arr);
        if (!$sqladd){
            xn_log('insert	table:'.$table.'	cond:'.xn_json_encode($arr),'empty_sql');
            return false;
        }
        $sql = ['sql'=>'INSERT INTO '.$table.' '.$sqladd['sql'],'val'=>$sqladd['val']];
        return $this->exec($sql);
    }

    /**
     * User: zhixiang
     *  Explain:
     *  - 批量插入数据
     *
     * @param      $table 表名
     * @param      $arr   array('字段1'=>数据1,'字段2'=>数据2)
     * @param null $d
     *
     * @return bool|int|mixed|resource
     */
    public function insert_batch($table, $arr)
    {
        if (!$arr) return false;
        $_arr = [];
        $columns = [];
        foreach ($arr as $data) {
            foreach ($data as $key => $value) {
                $columns[] = $key;
            }
        }
        $columns = array_unique($columns);
        foreach ($arr as $k => $v) {
            $sql_arr = [];
            foreach ($columns as $_v) {
                $sql_arr[] = $v[$_v];
            }
            $_arr[] = '(\'' . implode('\',\'', $sql_arr) . '\')';
        }
        return $this->exec(['sql'=>"INSERT INTO $table (" . implode(',', $columns) . ") VALUES " . implode(',', $_arr),'val'=>[]]);
    }

    public function replace(string $table,array $arr):bool
    {
        $sqladd = db_array_to_insert_sqladd($arr);
        if (!$sqladd){
            xn_log('replace	table:'.$table.'	cond:'.$sqladd,'empty_sql');
            return false;
        }
        return $this->exec(['sql'=>"REPLACE INTO $table ".$sqladd['sql'],'val'=>$sqladd['val']]);
    }

    public function update(string $table,array $cond,array $update):bool
    {
        $condadd = db_cond_to_sqladd($cond);
        $sqladd = db_array_to_update_sqladd($update);
        if (!$sqladd){
            xn_log('update	table:'.$table.' cond:'.$condadd['sql'].'	update:'.$sqladd['sql'],'empty_sql');
            return false;
        }
        return $this->exec(['sql'=>"UPDATE $table SET  ".$sqladd['sql'] .' '.$condadd['sql'],'val'=>array_merge($sqladd['val'],$condadd['val'])]);
    }

    public function delete(string $table,array $cond)
    {
        $condadd = db_cond_to_sqladd($cond);
        if (!$condadd){
            xn_log('update	table:'.$table.'	cond:'.$condadd['sql'],'empty_sql');
            return false;
        }
        return $this->exec(['sql'=>"DELETE FROM $table ".$condadd['sql'],'val'=>$condadd['val']]);
    }

    public function version()
    {
        $r = $this->sql_find_one(['sql'=>"SELECT VERSION() AS v",'val'=>[]]);
        return $r['v'];
    }

}
?>